create or replace package AC_GEN_VIEW is
 
  function CreateNewReport(p_view_name varchar2) return number;

  procedure AddParamColumn(p_view_header_id number, p_view_column_name varchar2, p_view_column_type varchar2);
  
  procedure DelParamColumn(p_view_header_id number, p_id number);
  
  procedure BuildSQL( p_owner varchar2, p_view_header_id number);

end AC_GEN_VIEW;
/

create or replace package body AC_GEN_VIEW is

   gc_view_templ varchar2(4000) :=
q'!create or replace view #VIEW_NAME# as
 with vv as
 (select
   #COLUMNS_ALL#  
  from #ORIG_VIEW_NAME# aa
  )
  select 
      #COLUMNS_NOT_PARAMS#  
       U_NAME,
       U_VALUE,
       '#VIEW_NAME#' src_view_name
  from vv UNPIVOT INCLUDE NULLS(u_value FOR u_name IN(
                                                       #COLUMNS_PARAMS#  
						                                         )
	                             )
!';

   gc_number_col  varchar2(4000) := 'to_char(#COLUMN#) #COLUMN#';
   
   gc_clob_col    varchar2(4000) := 
q'!
 ac_utils.hs(#COLUMN#) #COLUMN#_hs,
 dbms_lob.substr(#COLUMN#,4000, 1)        #COLUMN#_sr1,
 dbms_lob.substr(#COLUMN#,4000, 4000+1 )  #COLUMN#_sr2,
 dbms_lob.substr(#COLUMN#,4000, 8000+1 )  #COLUMN#_sr3,
 dbms_lob.substr(#COLUMN#,4000, 12000+1)  #COLUMN#_sr4
 !';

   function CreateNewReport(p_view_name varchar2) return number is
     v_ac_view_header_id number;
   begin
     v_ac_view_header_id :=   acc_seq.nextval;
     
     insert into ac_view_header
     (id,view_name)
     values
     (v_ac_view_header_id, p_view_name);
     
     return v_ac_view_header_id;
   end CreateNewReport;

   procedure AddParamColumn(p_view_header_id number, p_view_column_name varchar2, p_view_column_type varchar2) is
   begin
      insert into ac_view
      (id,                    column_name,        column_type,    view_header_id)
      values
      (acc_seq.nextval, p_view_column_name, p_view_column_type, p_view_header_id );
   end AddParamColumn;

   procedure DelParamColumn(p_view_header_id number, p_id number) is
   begin
      delete ac_view v
      where id = p_id
        and v.view_header_id = p_view_header_id;
            
   end DelParamColumn;
  
   function getViewName(p_view_name varchar2) return varchar2 is
   v_view_name varchar2(100);
   begin
     v_view_name := replace(upper(p_view_name), 'APEX_', 'VAC_');  
     return v_view_name; 
   end  getViewName;

   function getAllColumns(p_owner varchar2, p_view_name varchar2) return varchar2 is
   v_columns varchar2(32767);
   v_column  varchar2(32767);
   v_cr      varchar2(2) := chr(10);
   begin
     v_columns := null;
     for r in ( select t.COLUMN_NAME, t.DATA_TYPE
                from ALL_TAB_COLS t
                 where t.OWNER = p_owner
                   and t.TABLE_NAME = p_view_name
                 order by t.COLUMN_NAME
              ) loop
      
      if r.data_type = 'NUMBER' then
       v_column  := replace(gc_number_col, '#COLUMN#', r.column_name) ;
      elsif  r.data_type = 'CLOB' then
       v_column  := replace(gc_clob_col,   '#COLUMN#', r.column_name) ;
      else
       v_column  := r.column_name;
      end if;
      
      v_column  := v_column ||','||v_cr;
      
      v_columns := v_columns || v_column;
      
     end loop;
     
     return v_columns;  
   end  getAllColumns;

   function getNotParamColumns( p_owner varchar2, p_view_header_id number) return varchar2 is
   v_columns varchar2(32767);
   v_column  varchar2(32767);
   v_cr      varchar2(2) := chr(10);
   begin
     v_columns := null;
     for r in ( select t.COLUMN_NAME, t.DATA_TYPE
                            from ALL_TAB_COLS t
                             where t.OWNER = p_owner
                               and t.TABLE_NAME = 
                               (
                                 select vh.view_name 
                                 from ac_view_header vh
                                 where vh.id = p_view_header_id
                               )
                               and t.COLUMN_NAME not in
                               (
                                 select vc.column_name 
                                 from ac_view vc
                                 where vc.view_header_id = p_view_header_id
                               )
                             order by t.COLUMN_NAME      
               ) loop
       v_column  :=  r.column_name  ||','||v_cr;
       v_columns := v_columns || v_column;
      end loop;
      
      return v_columns;
   end  getNotParamColumns;

   function getParamColumns( p_owner varchar2, p_view_header_id number) return varchar2 is
   v_columns varchar2(32767);
   v_column  varchar2(32767);
   v_cr      varchar2(2) := chr(10);
   begin
     v_columns := null;
     for r in ( select t.COLUMN_NAME, t.COLUMN_TYPE
                from ac_view t
                where t.view_header_id = p_view_header_id
                order by t.COLUMN_NAME      
              ) loop
       if r.column_type = 'CLOB' then
        v_column  :=  r.column_name  ||'_HS,'||v_cr;
       else
        v_column  :=  r.column_name  ||','   ||v_cr;
       end if;        
       v_columns := v_columns || v_column;
     end loop;
      
     return v_columns;
   end  getParamColumns;

   procedure BuildSQL(p_owner varchar2, p_view_header_id number) is
    v_view_name          varchar2(100);
    v_orig_view_name     varchar2(100);
    v_src                clob;
    v_columns            clob;
    v_param_columns      clob;
    v_param_all_columns  clob;
    v_not_param_columns  clob;
   begin
     select h.view_name 
       into v_orig_view_name   
     from ac_view_header h
     where h.id = p_view_header_id;
     
     v_view_name := getViewName(v_orig_view_name);
     v_src       := replace(gc_view_templ, '#VIEW_NAME#',      v_view_name);
     v_src       := replace(v_src,         '#ORIG_VIEW_NAME#', v_orig_view_name);
     
     v_param_all_columns := getAllColumns     (p_owner, v_orig_view_name);
     v_not_param_columns := getNotParamColumns(p_owner, p_view_header_id);
     v_param_columns     := getParamColumns   (p_owner, p_view_header_id);
                            
     v_src       := replace(v_src,  '#COLUMNS_NOT_PARAMS#', v_not_param_columns);
     v_src       := replace(v_src,  '#COLUMNS_PARAMS#',     v_param_columns    ); 
     v_src       := replace(v_src,  '#COLUMNS_ALL#',        v_param_all_columns); 
     
     update ac_view_header h
     set h.view_src = v_src
     where h.id = p_view_header_id;
     
   end  BuildSQL;
end AC_GEN_VIEW;
/

